import datetime
import os
import re
import sys
import traceback

from logger_api import logger
import merage_config
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Alignment
from openpyxl.utils import get_column_letter, column_index_from_string


def check_empty(row):
    return str(row[0].value).strip() != ""


def format_data(input_file):
    print("\n读取收集数据")

    # repeat_product_name = []
    for file_name in os.listdir(input_file):
        sheet_name = '产品表'  # 或者使用索引，例如 sheet_name = 0

        # 读取选定的表格数据
        df = pd.read_excel(os.path.join(input_file, file_name), sheet_name=sheet_name, header=None)
        df.drop(index=[0, 1], inplace=True)

        for index, df_rows_value in enumerate(df.values):
            temp_dict = {}

            for column_name, column_letter in merage_config.source_column.items():

                # 排除重复
                temp_column_number = column_index_from_string(merage_config.source_column.get("ProNumber")) - 1
                temp_data = df_rows_value[temp_column_number]
                # if temp_data in repeat_product_name:
                #     logger.debug("文件名: %s 产品名: %s 行号: %s 重复" % (
                #         os.path.join(input_file, file_name), df_rows_value[2], index))
                #     break

                # 主线
                column_number = column_index_from_string(column_letter) - 1
                data = df_rows_value[column_number]

                # 过滤

                # if column_name == "ProName":
                #     if not pd.isna(data) and data:
                #         repeat_product_name.append(data)
                #     else:
                #         temp_dict = {}
                #         break
                if column_name == "NumberOfItemsValue":
                    if not (not pd.isna(data) and data):
                        if not pd.isna(df_rows_value[2]):
                            logger.debug("文件名: %s 产品名: %s 行号: %s 数据为空" % (
                                os.path.join(input_file, file_name), df_rows_value[2], index))
                        temp_dict = {}
                        break

                if re.match("^\d+\.\d{3,}", str(data)):
                    data = round(data, 2)
                temp_dict[column_name] = data
            if temp_dict:
                yield temp_dict
            # print(column_name, " ",column_number, " 数据=> ", df_rows_value[column_number])

    # print(finally_product_data)


def get_product():
    try:
        map_list = []
        for product_detail in format_data("insert_file"):
            print(product_detail)
            map_list.append(product_detail)

        result_data = []
        for item in map_list:
            if pd.isna(item.get('NumberOfItemsValue')):
                break
            print(item)
            result_data.append({'goods_carton_number': item.get('NumberOfItemsValue'),
                                'goods_carton_size': "%s,%s,%s" % (
                                0 if pd.isna(item.get('ShippingWidthValue')) else item.get('ShippingWidthValue'),
                                0 if pd.isna(item.get('ShippingHeightValue')) else item.get('ShippingHeightValue'),
                                0 if pd.isna(item.get('ShippingLengthValue')) else item.get('ShippingLengthValue')
                                ),
                                'goods_carton_weight': int(float(item.get('ShippingWeightValue')) * 1000),
                                'goods_size': "%s,%s,%s" % (0 if pd.isna(item.get('SizesWidthValue')) else item.get('SizesWidthValue'), 0 if pd.isna(item.get('SizesHeightValue')) else item.get('SizesHeightValue'), 0 if pd.isna(item.get('SizesLengthValue')) else item.get('SizesLengthValue')),
                                'goods_weight': int(float(item.get('ItemWeightValue')) * 1000),
                                'goods_price': 0 if pd.isna(item.get('goods_price')) else int(float(item.get('goods_price')) * 1000),
                                'goods_number': item.get('ProNumber'),
                                'goods_cn_name': item.get('ProCNName'),
                                'goods_image': f'http://127.0.0.1:5000/api/v1/images/{item.get("ProNumber")}/{item.get("ProNumber")}.png',
                                'created_at': datetime.datetime.now().strptime(
                                    datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"), "%Y-%m-%d %H:%M:%S"),
                                'updated_at': datetime.datetime.now().strptime(
                                    datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"), "%Y-%m-%d %H:%M:%S"),
                                'factory_link': item.get('factory_link'),
                                'factory_name': item.get('factory_name'),
                                })

        print(result_data)
        return result_data
    except Exception as e:
        logger.error("hours job run error:" + e.__str__())
        logger.error(traceback.format_exc(15))


if __name__ == '__main__':
    # format_data("file")
    get_product()
